import pymysql
from ToDos.todos.todo1 import Todo
class todo_mysql():
    def conn(self):
        return pymysql.Connection(host='192.168.235.130', database='常用库', user='root' ,password='root@123ABC',autocommit=True)
    def init_bd(self):
        with self.conn() as conn:
            cursor = conn.cursor()
            cursor.execute('''
            CREATE TABLE IF not EXISTS `todos` (
            `id` int NOT NULL AUTO_INCREMENT,
            `title` varchar(255) NOT NULL DEFAULT '',
            `completed` int NOT NULL DEFAULT b'0',
            PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            ''')
    def obj(self,i):
        if i[2] == 0:
            date = Todo(i[1], i[0])
            return date
        elif i[2] == 1:
            date = Todo(i[1], i[0], True)
            return date
    def get_list(self):
        with self.conn() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos')
            data = cursor.fetchall()
            list = []
            for i in data:
                date = self.obj(i)
                list.append(date)
            return list
    def create(self,title:str):
        with self.conn() as conn:
            cursor = conn.cursor()
            cursor.execute('insert into todos(title) value(%s)',(title,))
            cursor.execute('select last_insert_id()')
            (id,) = cursor.fetchone()
            return Todo(title,id)

    def get_by_id(self, id):
        with self.conn() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where id=%s', (id))
            sar = cursor.fetchone()
            if sar is not None:
                data = self.obj(sar)
                return data
    def mark_completed(self,id):
        with self.conn() as conn:
            data = self.get_by_id(id)
            cursor = conn.cursor()
            if data is not None:
                cursor.execute('update todos set completed=1 where id=%s',(id))
                return data
    def del_todos(self, title):
        with self.conn() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where title=%s', (title))
            sar = cursor.fetchone()
            if sar is not None:
                cursor.execute('delete from todos where title=%s', (title))
                return sar



# td = todo_mysql()
# td.init_bd()
# td.create('生活11')
# td.get_list()
# td.mark_completed(2)
# td.del_todos('学习')
# td.get_by_id(1)